<%@ Page language="vb" CodeFile="searchClient.aspx.vb" Inherits="searchClient_aspx_vb" %>

<%
    rowNumber = 1
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        intClientID = Request.QueryString("clientID")
        If Len(intClientID) > 0 Then
            intClientID = CLng(intClientID)
            dbOpen()
            RecSet = Conn.Execute("Select c.countryCurrency, (Select Count(v.vehicleID) From tblVehicle v Where v.clientID = cl.clientID) As vehicleCount, (Select Count(c.claimID) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.clientID = cl.clientID) As claimCount, (Select Sum(c.paymentAmount) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.clientID = cl.clientID) As claimSum From (tblClient cl INNER JOIN tblCountry c ON c.countryID = cl.countryID) Where cl.clientID = " & intClientID & "")
            If Not RecSet.EOF Then
                countryCurrency = RecSet.Fields("countryCurrency").Value
                vehicleCount = RecSet.Fields("vehicleCount").Value
                claimCount = RecSet.Fields("claimCount").Value
                claimSum = RecSet.Fields("claimSum").Value
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(claimSum) Then
                claimSum = 0
            End If
        End If
        Over()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH LEASE TAKER<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchClient.aspx?subDo=search' method='post' onsubmit=""javascript:return checkFields();"">")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID'>")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_countryID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Lease taker type:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='clientType' id='clientType'>")
        Response.Write("<option value=''> Select option  ")
        Response.Write("<option value='1'> Private  ")
        Response.Write("<option value='2'> Company  ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Lease taker:</td><td align='right'><input type='text' name='client' id='client' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Lease taker ID:</td><td align='right'><input type='text' name='clientID' id='clientID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Corporate identity number:</td><td align='right'><input type='text' name='cin' id='cin' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Street:</td><td align='right'><input type='text' name='street' id='street' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Zipcode:</td><td align='right'><input type='text' name='zipcode' id='zipcode' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>City:</td><td align='right'><input type='text' name='city' id='city' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Areacode:</td><td align='right'><input type='text' name='areacode' id='areacode' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Telephone:</td><td align='right'><input type='text' name='telephone' id='telephone' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Email:</td><td align='right'><input type='text' name='email' id='email' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        If Len(clientID) > 0 Then 
            Response.Write("<tr><td class='headline' colspan='2'>LEASE TAKER STATISTICS<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of lease contracts:</td><td align='right'>" & vehicleCount & "</td></tr>")
            Response.Write("<tr><td>Number of claims reported:</td><td align='right'>" & claimCount & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total claim cost (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimSum), 2) & "</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End IF
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr><td class='headline' colspan='12'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        clientType = Request.Form("clientType")
        client = Request.Form("client")
        intClientID = Request.Form("clientID")
        cin = Request.Form("cin")
        street = Request.Form("street")
        zipcode = Request.Form("zipcode")
        city = Request.Form("city")
        areacode = Request.Form("areaCode")
        telephone = Request.Form("telephone")
        email = Request.Form("email")
        If Len(intClientID) = 0 Then
            intClientID = Request.QueryString("clientID")
        End If
        If Len(countryID) > 0 Then 
            sqlCountryID = " And cl.countryID = " & countryID & ""
        End If
       
        If Not IsDBNull(clientType) Then
            If Len(clientType) > 0 Then
                sqlClientType = " And cl.clientType = " & clientType & ""
            End If
        End If
        
        If Len(client) > 0 Then
            client = Trim(client)
            client = Replace(CStr(client), "'", "")
            sqlClient = " And cl.client Like '%" & client & "%'"
        End If
        If Len(intClientID) > 0 Then
            sqlClientID = " And cl.clientID = " & intClientID & ""
        End If
        If Len(cin) > 0 Then
            cin = Trim(cin)
            cin = Replace(cin, "'", "")
            sqlCin = " And cl.cin Like '%" & cin & "%'"
        End If
        If Len(street) > 0 Then
            street = Trim(street)
            street = Replace(street, "'", "")
            sqlStreet = " And cl.street Like '%" & street & "%'"
        End If
        If Len(zipcode) > 0 Then
            sqlZipcode = " And cl.zipcode Like '%" & zipcode & "%'"
        End If
        If Len(city) > 0 Then
            city = Trim(city)
            city = Replace(CStr(city), "'", "")
            sqlCity = " And cl.city Like '%" & city & "%'"
        End If
        If Len(areacode) > 0 Then
            areacode = Trim(areacode)
            areacode = Replace(areacode, "'", "")
            sqlAreacode = " And cl.areaCode Like '%" & areacode & "%'"
        End If
        If Len(telephone) > 0 Then
            telephone = Trim(telephone)
            telephone = Replace(telephone, "'", "")
            sqlTelephone = " And cl.telephone Like '%" & telephone & "%'"
        End If
        If Len(email) > 0 Then
            email = Trim(email)
            email = Replace(email, "'", "")
            sqlEmail = " And cl.email Like '%" & email & "%'"
        End If
        If Len(subDo) = 0 Then
            sqlCountryID = " And cl.countryID = 1000"
        End If
        dbOpen()
        RecSet = Conn.Execute("Select cl.clientID, cl.client, cl.clientType, cl.city, c.country From (tblClient cl INNER JOIN tblCountry c ON c.countryID = cl.countryID) Where cl.countryID > 0" & sqlCountryID & sqlClientType & sqlClient & sqlClientID & sqlStreet & sqlZipcode & sqlCity & sqlAreacode & sqlTelephone & sqlEmail & sqlCin & " Order By cl.client")
        If RecSet.EOF Then
            Response.Write("<tr><td><font color='red'>No customers found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Lease taker</td>")
            Response.Write("<td class='formBold'>Lease taker type</td>")
            Response.Write("<td class='formBold'>City</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                intClientID = RecSet.Fields("clientID").Value
                client = RecSet.Fields("client").Value
                clientType = RecSet.Fields("clientType").Value
                city = RecSet.Fields("city").Value
                country = RecSet.Fields("country").Value
                If Not IsDBNull(clientType) Then
                    If CInt(clientType) = 1 Then
                        clientType = "<font color='orange'>Private</font>"
                    Else
                        clientType = "<font color='Green'>Company</font>"
                    End If
                Else
                    clientType = "<font color='Green'>Company</font>"
                End If
                
                If rowNumber = 0 Then
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End If
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & client & "</td>")
                Response.Write("<td>" & clientType & "</td>")
                Response.Write("<td>" & city & "</td>")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td align='right'><a href='editClient.aspx?clientID=" & intClientID & "'>edit lease taker</a></td>")
                Response.Write("<td align='right'><a href='searchDriver.aspx?subDo=search&clientID=" & intClientID & "'>view drivers</a></td>")
                Response.Write("<td align='right'><a href='addDriver.aspx?clientID=" & intClientID & "'>add drivers</a></td>")
                Response.Write("<td align='right'><a href='searchClient.aspx?subDo=viewVehicles&clientID=" & intClientID & "'>view LC</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "viewVehicles" Then
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
            Response.Write("<tr><td class='headline' colspan='12'>LEASE CONTRACTS<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date AS tblVehicleDate, p.product, b.brand, m.model, c.country From ((((tblVehicle v INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where v.clientID = " & intClientID & " ORDER BY v.vehicleID")
            If RecSet.EOF Then
                Response.Write("<tr><td><font color='red'>No lease contracts found.</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Car make</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Country</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 0
                Do Until RecSet.EOF
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    tblVehicleDate = RecSet.Fields("tblVehicleDate").Value
                    product = RecSet.Fields("product").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    country = RecSet.Fields("country").Value
                    If rowNumber = 0 Then
                        strGrey = ""
                        rowNumber = 1
                    Else
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 0
                    End If
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & brand & " " & model & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & country & "</td>")
                    Response.Write("<td>" & Left(Today(), 10) & "</td>")
                    Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End If
        Response.Write("</table>")
        Under()
    End If
%>
